package com.bijie.ent_huaxiang

import java.util.Properties

import org.apache.spark.sql.{SaveMode, SparkSession}

object bijie_position_detail_info {
    def main(args: Array[String]): Unit = {
        val spark: SparkSession = SparkSession
          .builder()
          .master("local[*]")
          .appName("JDBCRead")
          .getOrCreate()


        // 专用的读
        val url = "jdbc:mysql://192.168.1.90:3306/cy_analysis_data"
        val table1 = "company_base_info"
        val table2 = "position_detail_info"
        //company_name|turn_over|total_assets|production_value|time|identification|


        val props: Properties = new Properties()
        props.setProperty("user", "root")
        props.setProperty("password", "bigData@123")

        spark.read.jdbc(url, table1, props).createOrReplaceTempView("t1")

        spark.sql(
            """
              |select
              | company_name,
              | identification
              |from t1
              |
              |
            """.stripMargin).createOrReplaceTempView("t2") //todo 三千企业 三千id
        //-----------------------------------------------------------------------------------------------
        //2015
        spark.sql(
            """
              |select
              |company_name,
              |cast((round(rand()*2)+1000) as decimal(4,0))  as company_nature,
              |'104103111' position_id,
              |'音效师' position,
              |round(rand()*9)+10 as need_num,
              |'开放' status,
              |round(rand()*9) as work_age,
              |round(rand()*6)+1 as education,
              |'江西' as work_area,
              |round(rand()*9) as salary,
              |round(rand()*9)+20 as age,
              |identification
              |from t2
              |
            """.stripMargin).createOrReplaceTempView("t11")

        //2016
        spark.sql(
            """
              |select
              |company_name,
              |cast((round(rand()*2)+1000) as decimal(4,0)) as company_nature,
              |'105100101' position_id,
              |'财务顾问' position,
              |round(rand()*9)+10 as need_num,
              |'开放' status,
              |round(rand()*9) as work_age,
              |round(rand()*6)+1 as education,
              |'广东' as work_area,
              |round(rand()*9) as salary,
              |round(rand()*9)+20 as age,
              |identification
              |from t2
              |
              |
            """.stripMargin).createOrReplaceTempView("t12")


        //2017
        spark.sql(
            """
              |select
              |company_name,
              |cast((round(rand()*2)+1000) as decimal(4,0)) as company_nature,
              |'105100106' position_id,
              |'会计师' position,
              |round(rand()*9)+10 as need_num,
              |'开放' status,
              |round(rand()*9) as work_age,
              |round(rand()*6)+1 as education,
              |'河南' as work_area,
              |round(rand()*9) as salary,
              |round(rand()*9)+20 as age,
              |identification
              |from t2
              |
              |
            """.stripMargin).createOrReplaceTempView("t13")

        //2018
        spark.sql(
            """
              |select
              |company_name,
              |cast((round(rand()*2)+1000) as decimal(4,0)) as company_nature,
              |'105100109' position_id,
              |'成本会计' position,
              |round(rand()*9)+10 as need_num,
              |'开放' status,
              |round(rand()*9) as work_age,
              |round(rand()*6)+1 as education,
              |'江苏' as work_area,
              |round(rand()*9) as salary,
              |round(rand()*9)+20 as age,
              |identification
              |from t2
              |
              |
              |
            """.stripMargin).createOrReplaceTempView("t14")


        //2019
        spark.sql(
            """
              |select
              |company_name,
              |cast((round(rand()*2)+1000) as decimal(4,0)) as company_nature,
              |'107101107' position_id,
              |'皮肤科医师' position,
              |round(rand()*9)+10 as need_num,
              |'开放' status,
              |round(rand()*9) as work_age,
              |round(rand()*6)+1 as education,
              |'湖北' as work_area,
              |round(rand()*9) as salary,
              |round(rand()*9)+20 as age,
              |identification
              |from t2
              |
              |
            """.stripMargin).createOrReplaceTempView("t15")

        //2015~2019
        spark.sql(
            """
              |select
              |	company_name,
              |	company_nature,
              |	position_id,
              |	position,
              |	need_num,
              |	status,
              |	work_age,
              |	education,
              |	work_area,
              |	salary,
              |	age,
              |	identification
              |from t11
              |
              |union all
              |select
              |	company_name,
              |	company_nature,
              |	position_id,
              |	position,
              |	need_num,
              |	status,
              |	work_age,
              |	education,
              |	work_area,
              |	salary,
              |	age,
              |	identification
              |from t12
              |
              |union all
              |select
              |	company_name,
              |	company_nature,
              |	position_id,
              |	position,
              |	need_num,
              |	status,
              |	work_age,
              |	education,
              |	work_area,
              |	salary,
              |	age,
              |	identification
              |from t13
              |
              |union all
              |select
              |	company_name,
              |	company_nature,
              |	position_id,
              |	position,
              |	need_num,
              |	status,
              |	work_age,
              |	education,
              |	work_area,
              |	salary,
              |	age,
              |	identification
              |from t14
              |
              |union all
              |select
              |	company_name,
              |	company_nature,
              |	position_id,
              |	position,
              |	need_num,
              |	status,
              |	work_age,
              |	education,
              |	work_area,
              |	salary,
              |	age,
              |	identification
              |from t15
            """.stripMargin).createOrReplaceTempView("t20")

        val df = spark.sql(
            """

select
    1 id,
	company_name,
	company_nature,
	position_id,
	position,
	need_num,
	status,
	work_age,
	education,
	work_area,
	salary,
	age,
	identification
from t20


            """.stripMargin)


        //data.show()


        df.write.mode(SaveMode.Append).jdbc(url, table2, props) //Overwrite  会把表格重新建表，mysql注释都没了
        spark.close()
    }
}